package org.example.shoppingmall.dao;

import org.example.shoppingmall.entity.Category;
import org.example.shoppingmall.entity.Goods;
import org.example.shoppingmall.entity.User;
import org.example.shoppingmall.util.HikariUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OpenDao {
    /**
     * 根据商品id检查商品是否存在
     * @param id
     * @return
     */
    public boolean isGoodsExist(Integer id){
        String sql = "SELECT * FROM tb_goods WHERE id = ?";
        try(Connection conn = HikariUtil.getConn();
            PreparedStatement ps = conn.prepareStatement(sql)){
            ps.setInt(1,id);
            try(ResultSet rs = ps.executeQuery()){
                if(rs.next()){
                    int count = rs.getInt(1);
                    return count > 0;
                }
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 根据id查找商品并返回
     * @param
     * @return
     */
    public List<Goods> goodsSearch(Integer page_size,Integer page){
        String sql = "SELECT * FROM tb_goods limit ?,?";
        List<Goods> list = new ArrayList<>();
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1,page_size*(page-1));
            ps.setInt(2,page_size);
            try (ResultSet rs = ps.executeQuery()) {

                while (rs.next()) {

                    Goods goods = new Goods();
                    goods.setId(rs.getInt("id"));
                    goods.setCategory_id(rs.getInt("category_id"));
                    goods.setName(rs.getString("name"));
                    goods.setPicture(rs.getString("picture"));
                    goods.setPrice(rs.getBigDecimal("price"));
                    goods.setResume(rs.getString("resume"));
                    goods.setStock(rs.getInt("stock"));
                    goods.setUser_id(rs.getInt("user_id"));
                    list.add(goods);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 记录有多少条商品数据
     * @param
     * @param
     * @return
     */
    public Integer goodsCount(){
        String sql = "SELECT * FROM tb_goods";
        int count = 0;
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    count++;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 获取所有类别
     * @return
     */
    public List<Category> getAllCategory(){
        String sql = "SELECT * FROM tb_category";
        List<Category> list = new ArrayList<>();
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Category category = new Category();
                    category.setId(rs.getInt("id"));
                    category.setCategory(rs.getString("category"));
                    list.add(category);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 根据类别展示商品
     * @param categoryId
     * @param page_size
     * @param page
     * @return
     */
    public List<Goods> goodsDisplayByCategory(Integer categoryId,Integer page_size,Integer page){
        String sql = "SELECT * FROM tb_goods WHERE category_id = ? limit ?,?";
        List<Goods> list = new ArrayList<>();
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1,categoryId);
            ps.setInt(2,page_size*(page-1));
            ps.setInt(3,page_size);
            try (ResultSet rs = ps.executeQuery()) {

                while (rs.next()) {

                    Goods goods = new Goods();
                    goods.setId(rs.getInt("id"));
                    goods.setCategory_id(rs.getInt("category_id"));
                    goods.setName(rs.getString("name"));
                    goods.setPicture(rs.getString("picture"));
                    goods.setPrice(rs.getBigDecimal("price"));
                    goods.setResume(rs.getString("resume"));
                    goods.setStock(rs.getInt("stock"));
                    goods.setUser_id(rs.getInt("user_id"));
                    list.add(goods);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 根据类别记录有多少条商品数据
     * @param
     * @param
     * @return
     */
    public Integer goodsCountByCategory(Integer category_id){
        String sql = "SELECT * FROM tb_goods WHERE category_id = ?";
        int count = 0;
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1,category_id);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    count++;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
    /**
     * 根据关键字记录有多少条商品数据
     * @param
     * @param
     * @return
     */
    public Integer goodsCountByKeyword(String keyword){
        String sql = "SELECT * FROM tb_goods WHERE name LIKE ?";
        int count = 0;
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1,"%" + keyword + "%");
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    count++;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    /**
     * 根据关键字查找商品
     * @param keyword
     * @return
     */
    public List<Goods> goodsSearchByKeyword(String keyword,Integer page_size,Integer page){
        String sql = "SELECT * FROM tb_goods WHERE name LIKE ? limit ?,?";
        List<Goods> list = new ArrayList<>();
        try (Connection conn = HikariUtil.getConn();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1,"%" + keyword + "%");
            ps.setInt(2,page_size*(page-1));
            ps.setInt(3,page_size);
            try (ResultSet rs = ps.executeQuery()) {

                while (rs.next()) {

                    Goods goods = new Goods();
                    goods.setId(rs.getInt("id"));
                    goods.setCategory_id(rs.getInt("category_id"));
                    goods.setName(rs.getString("name"));
                    goods.setPicture(rs.getString("picture"));
                    goods.setPrice(rs.getBigDecimal("price"));
                    goods.setResume(rs.getString("resume"));
                    goods.setStock(rs.getInt("stock"));
                    goods.setUser_id(rs.getInt("user_id"));
                    list.add(goods);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}
